using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SEO.Model;
using SEO.DBSqlHelp;
using System.Drawing;
using System.Windows.Forms.DataVisualization.Charting;
using System.IO;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: EDMMailReportDAL
    /// 完成日期：2011年7月29日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMMailReportDAL
    {

        /// <summary>
        /// 获取EDMMailReport表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<EDMMailReport> GetAllEDMMailReportDAL()
        {
            List<EDMMailReport> list = new List<EDMMailReport>();
            string strSql = "select * from EDMMailReport";
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailReport et = new EDMMailReport();
                    et.ReportId = int.Parse(dr["report_id"].ToString());
                    et.UserId = int.Parse(dr["user_id"].ToString());
                    et.Md5Id = dr["md5_id"].ToString();
                    et.ClientIp = dr["client_ip"].ToString();
                    et.ClientUrl = dr["client_url"].ToString();
                    et.IpAddress = dr["ip_address"].ToString();
                    et.VisitTime = dr["visit_time"].ToString();
                    et.VisitTimeMilli = int.Parse(dr["visit_time_milli"].ToString());
                    et.VisitType = dr["visit_type"].ToString();
                    et.SendTime = dr["send_time"].ToString();
                    et.UserEmail = dr["user_email"].ToString();
                    et.ContentId = int.Parse(dr["content_id"].ToString());
                    et.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    et.UpdateReadtime = dr["update_readtime"].ToString();
                    et.UpdateReadtimeMilli = int.Parse(dr["update_readtime_milli"].ToString());

                    list.Add(et);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailReport -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取EDMMailReport表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<EDMMailReport> GetEDMMailReportByWhereDAL(string strSql)
        {
            List<EDMMailReport> list = new List<EDMMailReport>();
            try
            {
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailReport et = new EDMMailReport();
                    et.ReportId = int.Parse(dr["report_id"].ToString());
                    et.UserId = int.Parse(dr["user_id"].ToString());
                    et.Md5Id = dr["md5_id"].ToString();
                    et.ClientIp = dr["client_ip"].ToString();
                    et.ClientUrl = dr["client_url"].ToString();
                    et.IpAddress = dr["ip_address"].ToString();
                    et.VisitTime = dr["visit_time"].ToString();
                    et.VisitTimeMilli = int.Parse(dr["visit_time_milli"].ToString());
                    et.VisitType = dr["visit_type"].ToString();
                    et.SendTime = dr["send_time"].ToString();
                    et.UserEmail = dr["user_email"].ToString();
                    et.ContentId = int.Parse(dr["content_id"].ToString());
                    et.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    et.UpdateReadtime = dr["update_readtime"].ToString();
                    et.UpdateReadtimeMilli = int.Parse(dr["update_readtime_milli"].ToString());

                    list.Add(et);
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailReport -> " + ex.Message);
            }
            return list;
        }

        /// <summary>
        /// 往EDMMailReport表中插入数据
        /// <param name="et">实例化EDMMailReport类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertEDMMailReportDAL(SEO.Model.EDMMailReport et)
        {
            int iNum = 0;
            string strSql = "insert into EDMMailReport(md5_id,user_id,client_ip,client_url,ip_address,visit_type,visit_time,visit_time_milli,send_time,user_email,content_id,smtp_id) " +
             " values('" + et.Md5Id + "'," + et.UserId + ",'" + et.ClientIp + "','" + et.ClientUrl + "','" + et.IpAddress + "','" + et.VisitType + "','" + et.VisitTime + "'," + et.VisitTimeMilli + ",'" + et.SendTime + "','" + et.UserEmail + "'," + et.ContentId + "," + et.SmtpId + ")";
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("往EDMMailReport表中插入数据 -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除EDMMailReport表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteEDMMailReportDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailReport -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 更新EDMMailReport表中数据
        /// </summary>
        /// <param name="et">实例化EDMMailReport类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMMailReportDAL(SEO.Model.EDMMailReport et)
        {
            int iNum = 0;
            string strSql = "update EDMMailReport set update_readtime='" + et.UpdateReadtime + "' and update_readtime_milli=" + et.UpdateReadtimeMilli + " where report_id=" + et.ReportId;
            try
            {
                iNum = SEO.DBSqlHelp.EDMDBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailReport -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表EDMMailReport到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindEDMMailReportDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                int iNum = 1;
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMMailReport");
                ds.Tables["EDMMailReport"].Columns.Add("MailName", typeof(string));
                ds.Tables["EDMMailReport"].Columns.Add("ViewReportID", typeof(string));
                for (int i = 0; i < ds.Tables["EDMMailReport"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EDMMailReport"].Rows[i];
                    dr["ViewReportID"] = iNum;
                    string strUrl = dr["client_url"].ToString().ToLower();
                    #region
                    if (strUrl.Contains("126"))
                    {
                        dr["MailName"] = "126邮箱";
                    }
                    else if (strUrl.Contains("163"))
                    {
                        dr["MailName"] = "163邮箱";
                    }
                    else if (strUrl.Contains("sina"))
                    {
                        dr["MailName"] = "sina邮箱";
                    }
                    else if (strUrl.Contains("sohu"))
                    {
                        dr["MailName"] = "sohu邮箱";
                    }
                    else if (strUrl.Contains("yeah"))
                    {
                        dr["MailName"] = "Yeah邮箱";
                    }
                    else if (strUrl.Contains("yahoo"))
                    {
                        dr["MailName"] = "雅虎邮箱";
                    }
                    else if (strUrl.Contains("139"))
                    {
                        dr["MailName"] = "139邮箱";
                    }
                    else if (strUrl.Contains("tom"))
                    {
                        dr["MailName"] = "TOM邮箱";
                    }
                    else if (strUrl.Contains("21cn"))
                    {
                        dr["MailName"] = "21cn邮箱";
                    }
                    else if (strUrl.Contains("qq"))
                    {
                        dr["MailName"] = "QQ邮箱";
                    }
                    else if (strUrl.Contains("gmail"))
                    {
                        dr["MailName"] = "Gmail邮箱";
                    }
                    else if (strUrl.Contains("hotmail"))
                    {
                        dr["MailName"] = "Hotmail邮箱";
                    }
                    else
                    {
                        dr["MailName"] = strUrl;
                    }
                    #endregion

                    iNum++;
                }
                dgv.DataSource = ds.Tables["EDMMailReport"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMMailReport -> " + ex.Message);
            }
        }

        /// <summary>
        /// 绘制邮件阅读数量图表
        /// </summary>
        /// <param name="chart">chart控件名称</param>
        /// <param name="strAxisYTitle">Y轴标题</param>
        /// <param name="bShowAsLabel">是否显示标记点的值</param>
        /// <param name="bShowMarker">是否显示标记点</param>
        /// <param name="strUserID">用户ID</param>
        public void ChartForMailVisit(System.Windows.Forms.DataVisualization.Charting.Chart chart, string strAxisYTitle, bool bShowAsLabel, bool bShowMarker,string strUserID,string strVisitType,string strDatePara)
        {
            try
            {
                chart.Series["Series1"].Points.Clear();
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "SELECT CONVERT(varchar(10), visit_time, 120) AS gdate, COUNT(report_id) AS cnum FROM EDMMailReport WHERE (visit_type=" + strVisitType + ") and (user_id = " + strUserID + ") and (" + strDatePara + ") GROUP BY CONVERT(varchar(10), visit_time, 120)";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strDate = dr["gdate"].ToString();
                    string strNum = dr["cnum"].ToString();

                    chart.Series["Series1"].Points.AddXY(strDate, strNum);
                }
                dr.Dispose();
                cn.Close();

                chart.Series["Series1"].IsVisibleInLegend = false;
                //chart.Series["Series1"].LegendText = "每日发布数量";
                chart.Series["Series1"].IsValueShownAsLabel = bShowAsLabel;//显示数据点上的值
                if (bShowMarker)
                {
                    chart.Series["Series1"].MarkerStyle = System.Windows.Forms.DataVisualization.Charting.MarkerStyle.Circle;//标记数据点的类型
                    chart.Series["Series1"].MarkerSize = 10;//标记数据点
                    chart.Series["Series1"].MarkerColor = Color.FromArgb(150, 203, 40);//标记点的颜色
                    chart.Series["Series1"].MarkerBorderColor = Color.FromArgb(255, 251, 222);
                    chart.Series["Series1"].MarkerBorderWidth = 1;
                }
                chart.Series["Series1"].Color = Color.FromArgb(150, 203, 40);//绘图主线的颜色
                chart.Series["Series1"].BorderWidth = 3;
                chart.Series["Series1"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;//绘图类型

                chart.ChartAreas[0].AxisX.MajorGrid.LineColor = Color.FromArgb(247, 227, 173);
                //chart.ChartAreas[0].AxisX.MajorGrid.Interval = 5;//网格间隔
                //chart.ChartAreas[0].AxisX.MinorGrid.Interval = 5;
                chart.ChartAreas[0].AxisY.MajorGrid.LineColor = Color.FromArgb(247, 227, 173);
                //chart.ChartAreas[0].AxisY.MajorGrid.Interval = 5;
                //chart.ChartAreas[0].AxisY.MinorGrid.Interval = 5;

                chart.ChartAreas[0].BackColor = Color.FromArgb(255, 251, 222); //绘图区背景色
                chart.ChartAreas[0].AxisX.LineColor = Color.FromArgb(123, 65, 16); //X轴颜色
                chart.ChartAreas[0].AxisX.LineWidth = 2; //X轴宽度
                if (strVisitType == "1")
                {
                    chart.ChartAreas[0].AxisX.Title = "已阅读邮件数走势图(" + DateTime.Now.ToString("yyyy年M月d日") + ") http://www.cloudin.so"; //X轴标题
                }
                else
                {
                    chart.ChartAreas[0].AxisX.Title = "已点击邮件链接走势图(" + DateTime.Now.ToString("yyyy年M月d日") + ") http://www.cloudin.so"; //X轴标题
                }
                chart.ChartAreas[0].AxisX.MajorTickMark.LineColor = Color.FromArgb(123, 65, 16);//X轴刻度颜色
                chart.ChartAreas[0].AxisX.MajorTickMark.LineWidth = 2;//X轴刻度宽度

                chart.ChartAreas[0].AxisY.LineColor = Color.FromArgb(123, 65, 16); //Y轴颜色
                chart.ChartAreas[0].AxisY.LineWidth = 2; //Y轴宽度
                chart.ChartAreas[0].AxisY.Title = strAxisYTitle;//Y轴标题
                chart.ChartAreas[0].AxisY.MajorTickMark.LineColor = Color.FromArgb(123, 65, 16);//Y轴刻度颜色
                chart.ChartAreas[0].AxisY.MajorTickMark.LineWidth = 2;//Y轴刻度宽度
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绘制发送微博历史记录图表 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 导出邮件阅读数量到Txt
        /// </summary>
        /// <returns>返回结果</returns>
        public string ExportMailVisitForTxt(string strUserID, string strVisitType)
        {
            string strReturnValue = "";
            try
            {
                int k = 1;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "SELECT CONVERT(varchar(10), visit_time, 120) AS gdate, COUNT(report_id) AS cnum FROM EDMMailReport WHERE (visit_type=" + strVisitType + ") and (user_id = " + strUserID + ") GROUP BY CONVERT(varchar(10), visit_time, 120)";
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    string strDate = dr["gdate"].ToString();
                    string strNum = dr["cnum"].ToString();
                    string strValue = k + "	" + strDate + "	" + strNum + "\r\n";
                    strReturnValue += strValue;
                    k++;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出邮件阅读数量到Txt -> " + ex.Message);
            }

            return "0	阅读日期	已阅读邮件数量\r\n" + strReturnValue;

        }

        /// <summary>
        /// 导出邮件阅读数量到Excel
        /// </summary>
        /// <param name="strPath">保存路径</param>
        /// <param name="strFileName">文件名</param>
        public void ExportMailVisitForExcel(string strPath, string strFileName,string strUserID,string strVisitType)
        {
            try
            {
                int k = 1;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                string strSql = "SELECT CONVERT(varchar(10), visit_time, 120) AS gdate, COUNT(report_id) AS cnum FROM EDMMailReport WHERE (visit_type=" + strVisitType + ") and (user_id = " + strUserID + ") GROUP BY CONVERT(varchar(10), visit_time, 120)";
                SqlDataAdapter da = new SqlDataAdapter(strSql, cn);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMMailReport");
                DataTable dt = ds.Tables["EDMMailReport"];

                string strPathName = strPath + strFileName;
                FileStream fs = new FileStream(strPathName, FileMode.Create, FileAccess.Write);
                StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
                sw.WriteLine("序号,阅读日期,已阅读邮件数量");
                foreach (DataRow dr in dt.Rows)
                {
                    sw.WriteLine(k + "," + dr["gdate"] + "," + dr["cnum"]);
                    k++;
                }
                sw.Close();
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(strPathName));
                System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被
                System.Web.HttpContext.Current.Response.WriteFile(strPathName); // 把文件流发送到客户端
                System.Web.HttpContext.Current.Response.End();

                da.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("导出邮件阅读数量到Excel -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取最大的Report ID
        /// </summary>
        /// <returns></returns>
        public int GetEDMMailReportMaxID()
        {
            int iNum = 0;
            SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
            cn.Open();
            string strSql = "select report_id from EDMMailReport order by report_id desc";
            SqlCommand cm = new SqlCommand(strSql, cn);
            SqlDataReader dr = cm.ExecuteReader();
            if (dr.Read())
            {
                iNum = int.Parse(dr["report_id"].ToString())+1;
            }
            else
            {
                iNum = 1;
            }
            dr.Dispose();
            cn.Close();

            return iNum;
        }

        /// <summary>
        /// 检测该邮件阅读唯一ID是否存在
        /// </summary>
        /// <param name="strGuidID"></param>
        /// <returns></returns>
        public bool CheckIsExistGuidID(string strGuidID,string strType)
        {
            bool bValue = false;
            SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
            cn.Open();
            string strSql = "select report_id from EDMMailReport where visit_type=" + strType + " and md5_id='" + strGuidID + "'";
            SqlCommand cm = new SqlCommand(strSql, cn);
            SqlDataReader dr = cm.ExecuteReader();
            if (dr.Read())
            {
                bValue = true;
            }
            else
            {
                bValue = false;
            }
            dr.Dispose();
            cn.Close();

            return bValue;
        }
    }
}
